package com.jdbc.dao;


import com.jdbc.dao.impl.jdbcdaoimpl;
import com.jdbc.entity.jdbc;
import com.jdbc.framemain.setupframe;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class jdbcdao implements jdbcdaoimpl {
    public int insertpeople(jdbc people) {

        int n = 0;
        String sql = "insert into people values (default,?,?)";
        Connection conn = setupframe.con;
        PreparedStatement ps = null;
        try {
            // 创建会话
            ps = conn.prepareStatement(sql);
            // 设置 ? 的值
            ps.setString(1, jdbc.getName());
            ps.setString(2, jdbc.getSexuality());

            // 执行sql
            n = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {     // 关闭数据库资源
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return n;
    }

    public int deleteBook(int id) {
        String sql = "delete from people where id=?";
        int n = 0;
        Connection conn = setupframe.con;
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql);
            // 设置 ? 的值
            ps.setInt(1, id);
            // 执行sql
            n =  ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return n;
    }

    public int updateBook(jdbc people) {
        String sql = "update people set name=?, sexuality=?, where id=?";
        int n = 0;
        Connection conn =  setupframe.con;
        PreparedStatement ps = null;
        try {

            // 创建会话
            ps = conn.prepareStatement(sql);
            ps.setString(1, jdbc.getName());
            ps.setString(2, jdbc.getSexuality());
            ps.setInt(4, jdbc.getId());
            n = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return n;
    }

    public jdbc selectPeopleByName(String name) {
        String sql = "select * from people where bookName=?";
        jdbc jdbc = null;
        Connection conn = setupframe.con;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 创建会话
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();

            if (rs.next()) {
                int id = rs.getInt("id");
                String name1 = rs.getString("name");
                String sexuality = rs.getString("sexuality");
                jdbc = new jdbc(id, name1, sexuality);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jdbc;
    }

    public List<String> selectAllPeople() {
        String sql = "select * from people";
        List<String> list = new ArrayList<>();
        Connection conn = setupframe.con;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            // 获取结果集
            rs = ps.executeQuery();
            // 遍历结果集

            while (rs.next()) {
                int id = rs.getInt("id");
               String name = rs.getString("name");
                String sexuality = rs.getString("sexuality");
               String s1="结果是"+" "+id+" "+name+" "+sexuality;

                list.add(s1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
    }

